package show;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import util.JdbcUtil;

public class Cost_show 
{
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private LinkedList<co> cost_info;
	
	public Cost_show(int id)
	{
	}
	/**
	 * 
	 * this is the method created for get the sum of the money which is input between a time
	 * 
	 * */
	public double showIn(Date a,Date b)
	{
		String sql = "select money from cost where time between '"+a+"' and '"+b+"' and direct=1";
		double sum = 0;
		conn = JdbcUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				sum+=rs.getDouble("money");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.release(conn,ps,rs);
		}
		return sum;
	}
	/**
	 * 
	 * this is the method created for get the sum of the money which is output between a time
	 * 
	 * */
	public double showOut(Date a,Date b)
	{
		String sql = "select money from cost where time between '"+a+"' and '"+b+"' and direct=0";
		double sum = 0;
		conn = JdbcUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				sum+=-rs.getDouble(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.release(conn,ps,rs);
		}
		return sum;
	}
	
	/**
	 * 
	 * this is the method created for get all the use of the money which is input or output between a time
	 * 
	 * */
	public LinkedList<co> getCost_info(Date a,Date b) {
		String sql = "select * from cost where time between '"+a+"' and '"+b+"'";
		cost_info = new LinkedList<co>();
		
		conn = JdbcUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				co c = new co(rs.getString(1),rs.getInt(2)!=0?"收入":"支出",rs.getDouble(3),rs.getDate(4));
				cost_info.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cost_info;
	}
	/**
	 * 
	 * this is the method created for get all input or output money information
	 * 
	 * */
	public LinkedList<co> getCost_info() {
		String sql = "select * from cost";
		cost_info = new LinkedList<co>();
		
		conn = JdbcUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				co c = new co(rs.getString(1),rs.getInt(2)!=0?"收入":"支出",rs.getDouble(3),rs.getDate(4));
				cost_info.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cost_info;
	}
	public double total(Date a,Date b)
	{
		return showOut(a,b)+showIn(a,b);
	}
	/**
	 * 
	 * main method ... for test
	 * 
	 * */
//	@SuppressWarnings("deprecation")
//	public static void main(String[] args) 
//	{
//		Cost_show c = new Cost_show(123);
//		System.out.println(c.getCost_info());
//		System.out.println(c.showOut(new Date(113,2,2), new Date(113,8,3)));
//		System.out.println(c.showIn(new Date(113,2,2), new Date(113,8,3)));
//		System.out.println(c.total(new Date(113,2,2), new Date(113,8,3)));
//	}
}
